/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Controllers;

import Models.Criminal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Tung
 */
public class CriminalController {

    static Connection conn = DBUtility.openConnection();

    //This method returns a collection of Criminals in Criminal table
    public static ArrayList<Criminal> GetCriminals() {
        try {
            ArrayList<Criminal> criminalList = new ArrayList<Criminal>();

            CallableStatement cs = conn.prepareCall("{call sp_GetCriminals}", ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet rs = cs.executeQuery();
            if (rs.next()) {
                //If there are result, the ResultSet is reseted, 
                //and each record will be added to the ArrayList and then get returned
                rs.beforeFirst();
                while (rs.next()) {
                    int _id = rs.getInt("CriminalID");
                    criminalList.add(new Criminal(_id));
//                    criminalList.add(GetCriminalByID(_id));
                }
                return criminalList;
            }
        } catch (SQLException ex) {
            Logger.getLogger(CriminalController.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    //I'm not sure if this method will be needed, but I'm still writing it anyway, just in case
    public static Criminal GetCriminalByID(int ID) {
        try {
            CallableStatement cs = conn.prepareCall("{call sp_GetCriminalByID(?)}");
            cs.setInt(1, ID);
            ResultSet rs = cs.executeQuery();
            //If there is indeed a criminal by this ID, the method will return an instance of that criminal
            if (rs.next()) {
                return new Criminal(rs.getInt("CriminalID"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(CriminalController.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public static boolean AddCriminal(Criminal newCriminal) {
        CallableStatement cs;
        try {
            cs = conn.prepareCall("{call sp_AddCriminal(?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
            cs.setString("CriminalFullName", newCriminal.getFullName());
            cs.setBoolean("CriminalGender", newCriminal.isGender());
            java.sql.Date _sqlDate = new java.sql.Date(newCriminal.getDateOfBirth().getTime());
            cs.setDate("CriminalDOB", _sqlDate);
            cs.setString("CriminalPicture", newCriminal.getPicture());
            cs.setString("PlaceOfBirth", newCriminal.getPlaceOfBirth());
            cs.setString("Nationality", newCriminal.getNationality());
            cs.setShort("Height", newCriminal.getHeight());
            cs.setShort("Weight", newCriminal.getWeight());
            cs.setString("Hair", newCriminal.getHair());
            cs.setString("Race", newCriminal.getRace());
            cs.setString("Aliases", newCriminal.getAliases());
            cs.setString("OtherDescriptions", newCriminal.getOtherDescriptions());
            cs.setBoolean("IsArrested", newCriminal.isIsArrested());
            _sqlDate = new java.sql.Date(newCriminal.getArrestedTime().getTime());
            cs.setDate("ArrestedTime", _sqlDate);

            if (cs.executeUpdate() > 0) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException ex) {
            Logger.getLogger(CriminalController.class.getName()).log(Level.SEVERE, null, ex);
        }
        return true;
    }

    public static boolean EditCriminal(Criminal newCriminal) {
        CallableStatement cs;
        try {
            cs = conn.prepareCall("{call sp_EditCriminal(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
            cs.setString("CriminalFullName", newCriminal.getFullName());
            cs.setBoolean("CriminalGender", newCriminal.isGender());
            java.sql.Date _sqlDate = new java.sql.Date(newCriminal.getDateOfBirth().getTime());
            cs.setDate("CriminalDOB", _sqlDate);
            cs.setString("CriminalPicture", newCriminal.getPicture());
            cs.setString("PlaceOfBirth", newCriminal.getPlaceOfBirth());
            cs.setString("Nationality", newCriminal.getNationality());
            cs.setShort("Height", newCriminal.getHeight());
            cs.setShort("Weight", newCriminal.getWeight());
            cs.setString("Hair", newCriminal.getHair());
            cs.setString("Race", newCriminal.getRace());
            cs.setString("Aliases", newCriminal.getAliases());
            cs.setString("OtherDescriptions", newCriminal.getOtherDescriptions());
            if (newCriminal.isIsArrested()) {
                cs.setBoolean("IsArrested", newCriminal.isIsArrested());
                _sqlDate = new java.sql.Date(newCriminal.getArrestedTime().getTime());
                cs.setDate("ArrestedTime", _sqlDate);
            } else {
                cs.setBoolean("IsArrested", newCriminal.isIsArrested());
                cs.setDate("ArrestedTime", null);
            }
            cs.setInt("CriminalID", newCriminal.getID());

            System.out.println(newCriminal.getID());

            if (cs.executeUpdate() > 0) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException ex) {
            Logger.getLogger(CriminalController.class.getName()).log(Level.SEVERE, null, ex);
        }
        return true;
    }

    public static ArrayList<Criminal> FilterCriminal(HashMap parametersMap) {
        CallableStatement cs;
        Date _date = null;
        ArrayList<Criminal> resultList = new ArrayList<Criminal>();
        try {
            cs = conn.prepareCall("{call sp_FilterCriminal(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}", ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            cs.setString("CriminalFullName", parametersMap.get("FullName").toString());
            cs.setString("CriminalGender", parametersMap.get("Gender").toString());
            _date = (Date) parametersMap.get("MinDOB");
            cs.setDate("MinDOB", new java.sql.Date(_date.getTime()));
            _date = (Date) parametersMap.get("MaxDOB");
            cs.setDate("MaxDOB", new java.sql.Date(_date.getTime()));
            cs.setString("PlaceOfBirth", (String) parametersMap.get("PlaceOfBirth").toString());
            cs.setString("Nationality", (String) parametersMap.get("Nationality").toString());
            cs.setInt("MinHeight", Integer.parseInt(parametersMap.get("MinHeight").toString()));
            cs.setInt("MaxHeight", Integer.parseInt(parametersMap.get("MaxHeight").toString()));
            cs.setInt("MinWeight", Integer.parseInt(parametersMap.get("MinWeight").toString()));
            cs.setInt("MaxWeight", Integer.parseInt(parametersMap.get("MaxWeight").toString()));
            cs.setString("Hair", parametersMap.get("Hair").toString());
            cs.setString("Race", parametersMap.get("Race").toString());
            cs.setString("Aliases", parametersMap.get("Aliases").toString());
            cs.setString("IsArrested", parametersMap.get("IsArrested").toString());
            _date = (Date) parametersMap.get("MinArrestedTime");
            cs.setDate("MinArrestedTime", new java.sql.Date(_date.getTime()));
            _date = (Date) parametersMap.get("MaxArrestedTime");
            cs.setDate("MaxArrestedTime", new java.sql.Date(_date.getTime()));
            
            ResultSet rs = cs.executeQuery();
            if (rs.next()) {
                rs.beforeFirst();
                while (rs.next()) {
                    int _id = rs.getInt("CriminalID");
                    resultList.add(new Criminal(_id));
                }
                return resultList;
            }
        } catch (SQLException ex) {
            Logger.getLogger(CriminalController.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public static ArrayList<Criminal> FilterCriminalNotArrested(HashMap parametersMap) {
        CallableStatement cs;
        Date _date = null;
        ArrayList<Criminal> resultList = new ArrayList<Criminal>();
        try {
            cs = conn.prepareCall("{call sp_FilterCriminalNotArrested(?,?,?,?,?,?,?,?,?,?,?,?,?,?)}", ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            cs.setString("CriminalFullName", parametersMap.get("FullName").toString());
            cs.setString("CriminalGender", parametersMap.get("Gender").toString());
            _date = (Date) parametersMap.get("MinDOB");
            cs.setDate("MinDOB", new java.sql.Date(_date.getTime()));
            _date = (Date) parametersMap.get("MaxDOB");
            cs.setDate("MaxDOB", new java.sql.Date(_date.getTime()));
            cs.setString("PlaceOfBirth", (String) parametersMap.get("PlaceOfBirth").toString());
            cs.setString("Nationality", (String) parametersMap.get("Nationality").toString());
            cs.setInt("MinHeight", Integer.parseInt(parametersMap.get("MinHeight").toString()));
            cs.setInt("MaxHeight", Integer.parseInt(parametersMap.get("MaxHeight").toString()));
            cs.setInt("MinWeight", Integer.parseInt(parametersMap.get("MinWeight").toString()));
            cs.setInt("MaxWeight", Integer.parseInt(parametersMap.get("MaxWeight").toString()));
            cs.setString("Hair", parametersMap.get("Hair").toString());
            cs.setString("Race", parametersMap.get("Race").toString());
            cs.setString("Aliases", parametersMap.get("Aliases").toString());
            cs.setString("IsArrested", parametersMap.get("IsArrested").toString());
            
            ResultSet rs = cs.executeQuery();
            if (rs.next()) {
                rs.beforeFirst();
                while (rs.next()) {
                    int _id = rs.getInt("CriminalID");
                    resultList.add(new Criminal(_id));
                }
                return resultList;
            }
        } catch (SQLException ex) {
            Logger.getLogger(CriminalController.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public static ResultSet getCriminalPerCase(int _ID) {
        CallableStatement cs;
        try {
            cs = conn.prepareCall("{call sp_GetCriminalPerCase(?)}", ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            cs.setInt("CriminalID", _ID);
            
            return cs.executeQuery();
        } catch (SQLException ex) {
            Logger.getLogger(CriminalController.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public static ResultSet getCaseDetailPerCriminal(int _criminalID, int _caseID) {
        CallableStatement cs;
        try {
            cs = conn.prepareCall("{call sp_GetCaseDetailPerCriminal(?,?)}", ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            cs.setInt("CriminalID", _criminalID);
            cs.setInt("CaseID", _caseID);
            
            return cs.executeQuery();
        } catch (SQLException ex) {
            Logger.getLogger(CriminalController.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
}
